drop proc spa_SEC_GetMenuItemForUser
go
/****** Object:  StoredProcedure [dbo].[spa_SEC_GetMenuItemForUser]    Script Date: 04/29/2011 16:32:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*********************************************************************************************
* PROCEDURE:	[spa_SEC_GetMenuItemForUser]
* PURPOSE: Get menu of a user
* NOTES:
* CREATED:	Nha Doan	04/01/2011
* MODIFIED:	
*/
/***************************************************************************************
 DATE			VERSION	TICKET	AUTHOR			DESCRIPTION
*----------------------------------------------------------------------------------------
* 04/01/2011	1.00	16131	Nha Doan 		Creation.
****************************************************************************************/
/*
Ex:
spa_SEC_GetMenuItemForUser '{21CAFCEA-8629-4FA9-9DC0-85A98AD344D2}'
*/
CREATE PROCEDURE [dbo].[spa_SEC_GetMenuItemForUser]
	@UserRecId uniqueidentifier
AS


--get all permissions of user (base on user's hierarchies)
select distinct b.PermissionCode into #PermissionCode from SEC_PermissionsInRole a (nolock) inner join SEC_Permissions b (nolock) on a.PermissionId = b.PermissionId or a.PermissionId is null
where a.RoleID in (select RoleID from SEC_UsersInRole (nolock) where UserRecID = @UserRecId) and b.PermissionId not in (select PermissionId from SEC_UserPermissionExcluded (nolock) where UserRecID = @UserRecId) 



declare @Code varchar(100)

create table #sitemap(
	SiteMapId int
	,Parent int
)
--get all sitemap ids that can access by the specified condition
declare code_cur cursor for
select PermissionCode FROM #PermissionCode
open code_cur
fetch next from code_cur into @Code
while @@fetch_status = 0
begin
	insert into #sitemap select SiteMapId,Parent from SEC_SiteMap 
	where (','+PermissionCodes+',' like '%,'+@Code+',%' or PermissionCodes is null or PermissionCodes = '')
   fetch next from code_cur into @Code
end
close code_cur
deallocate code_cur

--select distinct b.* from #sitemap a
--join SEC_SiteMap b on a.SiteMapId = b.SiteMapId
--where b.SiteMapId not in (select MenuId from SEC_UserMenuItemsExcluded where UserID=@UserRecId and ExcludedStatus='1')
--ORDER By b.NodeOrder

select distinct b.* from #sitemap a
join SEC_SiteMap b (nolock) on a.SiteMapId = b.SiteMapId
--left join (select MenuId from SEC_UserMenuItemsExcluded where UserID=@UserRecId and ExcludedStatus='1') c on b.SiteMapId = c.MenuId
--where c.MenuId is null
ORDER By b.NodeOrder
GO
